﻿use mioer
go

insert into mioer(StuName) values
('软件1班'),
('软件二班'),
('软件三班'),
('软件四班'),
('软件五班'),
('软件六班'),
('软件七班'),
('软件八班'),
('软件九班'),
('软件十班')

update Stu set StuName = '软件一班' where StuID = '1'

delete from Class where StuID = '10'

alter table Student add CreateDate datetime default(getdate())

insert into Student(StuID,StuName,StuSex,StuBirthday,StuPhone,StuAddress) values 
	(1,'一号','男','2002-1-1',12345670001,'贵州南'),
	(2,'二号','女','2002-1-2',12345670010,'贵州西'),
	(3,'三号','男','2002-1-17',12345670100,'贵州帅'),
	(4,'四号','男','2002-4-16',12345671000,'贵州帅'),
	(5,'五号','女','2002-1-15',12345671001,'贵州帅'),
	(6,'六号','男','2002-1-1',12345671010,'贵州帅'),
	(7,'七号','男','2002-1-1',12345671100,'贵州帅'),
	(8,'八号','男','2002-4-18',12345671101,'贵州帅'),
	(9,'九号','女','2002-1-1',12345671110,'贵州帅'),
	(1,'十号','男','2002-7-1',12345671111,'贵州帅'),
	(2,'壹号','男','2002-1-9',00001234567,'贵州帅'),
	(3,'贰号','男','2002-2-6',00011234567,'贵州帅'),
	(4,'叁号','女','2002-3-4',00101234567,'贵州帅'),
	(5,'肆号','男','2002-5-1',01001234567,'贵州帅'),
	(6,'伍号','男','2002-3-6',10001234567,'贵州帅'),
	(7,'陆号','男','2002-8-1',10011234567,'贵州帅'),
	(8,'柒号','男','2002-11-1',10101234567,'贵州帅'),
	(9,'捌号','女','2002-12-1',11001234567,'贵州帅'),
	(1,'玖号','男','2002-9-1',11011234567,'贵州帅'),
	(2,'拾号','女','2002-1-14',11101234567,'贵州帅')

update StuAge set CreateDate=getdate()

delete from StuAge Where StuID='6'

alter table Course add CourseType nvarchar(10) check(CourseType in ('专业课','公共课'))

insert into Course(StuName) values('语文'),
('数学'),
('英语'),
('体育'),
('化学'),
('物理')

select * from Course

update Course set CoueseCredit=4 where StuName='英语'

insert into Score(StuId,CourseId,Score) values 
	(1,1,99),
	(2,1,89),
	(3,1,98.23),
	(4,2,49),
	(5,2,69),
	(6,2,79),
	(7,3,98),
	(8,3,17),
	(9,3,49.5),
	(10,3,61),
	(11,3,92),
	(12,4,83),
	(13,4,46),
	(14,4,67),
	(15,4,58),
	(16,4,79.5),
	(17,5,68),
	(18,5,72),
	(19,5,63),
	(20,6,74)

delete from IX_table_1 where StuID ='1' or CourseID='1'

alter table Score add constraint DK_Score_Score default(0)
alter table Score add constraint CK_Score_Score check(Score>=0 and Score<=100)
